package br.com.crednove.dao;

import br.com.crednove.modelo.Cliente;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DAOCliente {

    private Conexao conexao;
    private ResultSet rs;
    private Cliente cliente;
    private ArrayList<Cliente> listaClientes;
    private int numRegistros;

    public DAOCliente() {
        this.conexao = new Conexao();
        this.numRegistros = 0;
        this.cliente = new Cliente();
        this.listaClientes = new ArrayList<>();
    }

    public boolean insert(Cliente cliente) {
        PreparedStatement pstm = null;
        //this.conexao.conectar();
        try {
            final String sql = "insert into cliente (fk_codImportacao, clie_cpf, clie_numBeneficio, clie_nome, clie_dataNasc, "
                    + "clie_inicioPag, clie_idBanco, clie_estado, clie_cidade, clie_bairro, clie_ddd, clie_telefone, "
                    + "clie_endereco, clie_cep, clie_trabalhado, clie_exportado) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pstm = this.conexao.getConector().prepareStatement(sql);

            pstm.setInt(1, cliente.getCodImportacao());
            pstm.setString(2, cliente.getCpf());
            pstm.setString(3, cliente.getNumBeneficio());
            pstm.setString(4, cliente.getNome());
            pstm.setString(5, cliente.getDataNasc());
            pstm.setString(6, cliente.getInicioPag());
            pstm.setString(7, cliente.getIdBanco());
            pstm.setString(8, cliente.getEstado());
            pstm.setString(9, cliente.getCidade());
            pstm.setString(10, cliente.getBairro());
            pstm.setString(11, cliente.getDdd());
            pstm.setString(12, cliente.getTelefone());
            pstm.setString(13, cliente.getEndereco());
            pstm.setString(14, cliente.getCep());
            pstm.setBoolean(15, cliente.isTrabalhado());
            pstm.setBoolean(16, cliente.isExportado());

            pstm.executeUpdate();
            pstm.close();
            return true;

        } catch (SQLException erro) {
            //System.out.println(erro);
            return false;
        } finally {
            //this.conexao.desconectar();
        }
    }

    public boolean executaSQL(String sql) {
        boolean retorno = false;
        Statement stm = null;
        //this.conexao.conectar();
        try {
            stm = this.conexao.getConector().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            this.rs = stm.executeQuery(sql);
            this.rs.last();
            this.numRegistros = this.rs.getRow();
            this.rs.beforeFirst();
            retorno = true;
        } catch (Exception ex) {
            System.out.println(ex);
        } finally {
            return retorno;
        }
    }

    public Cliente select(String cpf) {
        this.cliente.limparInformacoes();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            final String sql = "select * from cliente where clie_cpf=(?)";
            pstm = this.conexao.getConector().prepareStatement(sql);
            pstm.setString(1, cpf);
            rs = pstm.executeQuery();
            this.cliente.setCpf(cpf);
            if (rs.next()) {
                this.cliente.setCodImportacao(rs.getInt(1));
                this.cliente.setNumBeneficio(rs.getString(3));
                this.cliente.setNome(rs.getString(4));
                this.cliente.setDataNasc(rs.getString(5));
                this.cliente.setInicioPag(rs.getString(6));
                this.cliente.setIdBanco(rs.getString(7));
                this.cliente.setEstado(rs.getString(8));
                this.cliente.setCidade(rs.getString(9));
                this.cliente.setBairro(rs.getString(10));
                this.cliente.setDdd(rs.getString(11));
                this.cliente.setTelefone(rs.getString(12));
                this.cliente.setEndereco(rs.getString(13));
                this.cliente.setCep(rs.getString(14));
                this.cliente.setTrabalhado(rs.getBoolean(15));
                this.cliente.setExportado(rs.getBoolean(16));
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            return this.cliente;
        }
    }

    public ArrayList<Cliente> select(String campo, String valor, boolean valorIgual) {
        this.listaClientes.clear();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        this.conexao.conectar();
        try {
            String sql = "";
            if (valorIgual) {
                sql = "select * from cliente where " + campo + "=(?)";
            } else {
                sql = "select * from cliente where " + campo + "!=(?)";
            }
            pstm = this.conexao.getConector().prepareStatement(sql);
            pstm.setString(1, valor);
            rs = pstm.executeQuery();

            while (rs.next()) {
                Cliente cl = new Cliente();

                cl.setCodImportacao(rs.getInt(1));
                cl.setCpf(rs.getString(2));
                cl.setNumBeneficio(rs.getString(3));
                cl.setNome(rs.getString(4));
                cl.setDataNasc(rs.getString(5));
                cl.setInicioPag(rs.getString(6));
                cl.setIdBanco(rs.getString(7));
                cl.setEstado(rs.getString(8));
                cl.setCidade(rs.getString(9));
                cl.setBairro(rs.getString(10));
                cl.setDdd(rs.getString(11));
                cl.setTelefone(rs.getString(12));
                cl.setEndereco(rs.getString(13));
                cl.setCep(rs.getString(14));
                cl.setTrabalhado(rs.getBoolean(15));
                cl.setExportado(rs.getBoolean(16));
                this.listaClientes.add(cl);
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        } finally {
            this.conexao.desconectar();
            return this.listaClientes;
        }
    }

    public Cliente importarCliente() {
        try {
            this.cliente.limparInformacoes();
            this.cliente.setCodImportacao(rs.getInt(1));
            this.cliente.setCpf(rs.getString(2));
            this.cliente.setNumBeneficio(rs.getString(3));
            this.cliente.setNome(rs.getString(4));
            this.cliente.setDataNasc(rs.getString(5));
            this.cliente.setInicioPag(rs.getString(6));
            this.cliente.setIdBanco(rs.getString(7));
            this.cliente.setEstado(rs.getString(8));
            this.cliente.setCidade(rs.getString(9));
            this.cliente.setBairro(rs.getString(10));
            this.cliente.setDdd(rs.getString(11));
            this.cliente.setTelefone(rs.getString(12));
            this.cliente.setEndereco(rs.getString(13));
            this.cliente.setCep(rs.getString(14));
            this.cliente.setTrabalhado(rs.getBoolean(15));
            this.cliente.setExportado(rs.getBoolean(16));
        } catch (SQLException ex) {
        } finally {
            return cliente;
        }
    }

    public boolean delete(String cpf) {
        PreparedStatement pstm = null;
        this.conexao.conectar();
        try {
            final String sql = "delete from cliente where clie_cpf=(?)";
            pstm = this.conexao.getConector().prepareStatement(sql);
            pstm.setString(1, cpf);
            pstm.executeUpdate();
            pstm.close();
            return true;
        } catch (Exception erro) {
            System.out.println(erro);
            return false;
        } finally {
            this.conexao.desconectar();
        }
    }

    public boolean atualizaTrabalhado(String cpf, boolean trabalhado) {
        PreparedStatement pstm = null;
        //this.conexao.conectar();
        try {
            final String sql = "update cliente set clie_trabalhado=(?) where clie_cpf=(?)";
            pstm = this.conexao.getConector().prepareStatement(sql);
            pstm.setBoolean(1, trabalhado);
            pstm.setString(2, cpf);
            pstm.executeUpdate();
            pstm.close();
            return true;
        } catch (Exception erro) {
            return false;
        } finally {
            //this.conexao.desconectar();
        }
    }
    
    public boolean atualizaExportado(String cpf, boolean exportado) {
        PreparedStatement pstm = null;
        //this.conexao.conectar();
        try {
            final String sql = "update cliente set clie_exportado=(?) where clie_cpf=(?)";
            pstm = this.conexao.getConector().prepareStatement(sql);
            pstm.setBoolean(1, exportado);
            pstm.setString(2, cpf);
            pstm.executeUpdate();
            pstm.close();
            return true;
        } catch (Exception erro) {
            return false;
        } finally {
            //this.conexao.desconectar();
        }
    }

    public int getNumRegistros() {
        return numRegistros;
    }

    public ResultSet getRs() {
        return rs;
    }

    public void conectar() {
        this.conexao.conectar();
    }

    public void desconectar() {
        this.conexao.desconectar();
    }
}
